<!DOCTYPE HTML>
<html>

<head>
	<link rel="bookmark"  type="image/x-icon"  href="/img/logo_miccall.png"/>
	<link rel="shortcut icon" href="/img/logo_miccall.png">
	
			    <title>
    Ame雨
    </title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no" />
    <link rel="stylesheet" href="/amelin-ting/css/mic_main.css" />
    <link rel="stylesheet" href="/amelin-ting/css/dropdownMenu.css" />
    <meta name="keywords" content="Ame雨" />
    
    	<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>
	 
    <noscript>
        <link rel="stylesheet" href="/amelin-ting/css/noscript.css" />
    </noscript>
    <style type="text/css">
        body:before {
          content: ' ';
          position: fixed;
          top: 0;
          background: url('/amelin-ting/img/bg.jpg') center 0 no-repeat;
          right: 0;
          bottom: 0;
          left: 0;
          background-size: cover; 
        }
    </style>

			    
  
    <script type="text/x-mathjax-config">
      MathJax.Hub.Config({
        tex2jax: {
          inlineMath: [ ['$','$'], ["\\(","\\)"]  ],
          processEscapes: true,
          skipTags: ['script', 'noscript', 'style', 'textarea', 'pre', 'code']
        }
      });
    </script>

    <script type="text/x-mathjax-config">
      MathJax.Hub.Queue(function() {
        var all = MathJax.Hub.getAllJax(), i;
        for (i=0; i < all.length; i += 1) {
          all[i].SourceElement().parentNode.className += ' has-jax';
        }
      });
    </script>
    <script async type="text/javascript" src="//cdn.bootcss.com/mathjax/2.7.1/latest.js?config=TeX-AMS-MML_HTMLorMML"></script>
  


    <script src="/amelin-ting/js/jquery.min.js"></script>
    <script src="/amelin-ting/js/jquery.scrollex.min.js"></script>
    <script src="/amelin-ting/js/jquery.scrolly.min.js"></script>
    <script src="/amelin-ting/js/skel.min.js"></script>
    <script src="/amelin-ting/js/util.js"></script>
    <script src="/amelin-ting/js/main.js"></script>
	
<meta name="generator" content="Hexo 5.4.0"></head>
    
		
<!-- Layouts -->



<!--  代码渲染  -->
<link rel="stylesheet" href="/css/prism_coy.css" />
<link rel="stylesheet" href="/css/typo.css" />
<!-- 文章页 -->
<body class="is-loading">
    <!-- Wrapper 外包 s-->
    <div id="wrapper" class="fade-in">
        <!-- Intro 头部显示 s -->
        <!-- Intro 头部显示 e -->
        <!-- Header 头部logo start -->
        <header id="header">
    <a href="/amelin-ting/" class="logo">Art Space</a>
</header>
        <!-- Nav 导航条 start -->
        <nav id="nav" class="special" >
            <ul class="menu links" >
			<!-- Homepage  主页  --> 
			<li >
	            <a href="/amelin-ting/" rel="nofollow">主页</a>
	        </li>
			<!-- categories_name  分类   --> 
	        
	        <li class="active">
	            <a href="#s1">分类</a>
	                    <ul class="submenu">
	                        <li>
	                        <a class="category-link" href="/amelin-ting/categories/Go/">Go</a></li><li><a class="category-link" href="/amelin-ting/categories/django/">django</a></li><li><a class="category-link" href="/amelin-ting/categories/drf/">drf</a></li><li><a class="category-link" href="/amelin-ting/categories/gin/">gin</a></li><li><a class="category-link" href="/amelin-ting/categories/go/">go</a></li><li><a class="category-link" href="/amelin-ting/categories/linux/">linux</a></li><li><a class="category-link" href="/amelin-ting/categories/mysql/">mysql</a></li><li><a class="category-link" href="/amelin-ting/categories/python/">python</a></li><li><a class="category-link" href="/amelin-ting/categories/%E5%86%85%E7%BD%91%E7%A9%BF%E9%80%8F/">内网穿透</a></li><li><a class="category-link" href="/amelin-ting/categories/%E5%89%8D%E7%AB%AF/">前端</a>
	                    </ul>
	        </li>
	        
	        <!-- archives  归档   --> 
	        
	        
		        <!-- Pages 自定义   -->
		        
		        <li>
		            <a href="/about/" title="简历">
		                简历
		            </a>
		        </li>
		        
		        <li>
		            <a href="/group/" title="团队">
		                团队
		            </a>
		        </li>
		        
		        <li>
		            <a href="/gallery/" title="图库">
		                图库
		            </a>
		        </li>
		        
		        <li>
		            <a href="/tag/" title="标签">
		                标签
		            </a>
		        </li>
		        


            </ul>
            <!-- icons 图标   -->
			<ul class="icons">
                    
                    <li>
                        <a title="github" href="https://github.com/miccall" target="_blank" rel="noopener">
                            <i class="icon fa fa-github"></i>
                        </a>
                    </li>
                    
                    <li>
                        <a title="500px" href="http://500px.com" target="_blank" rel="noopener">
                            <i class="icon fa fa-500px"></i>
                        </a>
                    </li>
                    
			</ul>
</nav>

        <div id="main" >
            <div class ="post_page_title_img" style="height: 25rem;background-image: url(https://img1.baidu.com/it/u=464477847,1248192382&amp;fm=26&amp;fmt=auto);background-position: center; background-repeat:no-repeat; background-size:cover;-moz-background-size:cover;overflow:hidden;" >
                <a href="#" style="padding: 4rem 4rem 2rem 4rem ;"><h2 >mysqlCMD命令</h2></a>
            </div>
            <!-- Post -->
            <div class="typo" style="padding: 3rem;">
                <h2 id="一、存储数据的演变过程"><a href="#一、存储数据的演变过程" class="headerlink" title="一、存储数据的演变过程"></a>一、存储数据的演变过程</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br></pre></td><td class="code"><pre><span class="line"><span class="number">1.</span>随意的存到一个文件中、数据格式也是千差万别的完全取决于我们自己</span><br><span class="line">    <span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">    # 小李</span></span><br><span class="line"><span class="string">    jason|123|NB</span></span><br><span class="line"><span class="string">    # 小王</span></span><br><span class="line"><span class="string">    egon-123-DBJ</span></span><br><span class="line"><span class="string">    # 小红</span></span><br><span class="line"><span class="string">    tank~123~hecha</span></span><br><span class="line"><span class="string">    &quot;&quot;&quot;</span></span><br><span class="line">    </span><br><span class="line"><span class="number">2.</span>软件开发目录规范</span><br><span class="line">	限制了存储数据的具体位置</span><br><span class="line">        <span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">        bin</span></span><br><span class="line"><span class="string">        conf</span></span><br><span class="line"><span class="string">        core</span></span><br><span class="line"><span class="string">        db</span></span><br><span class="line"><span class="string">        lib</span></span><br><span class="line"><span class="string">        readme</span></span><br><span class="line"><span class="string">        &quot;&quot;&quot;</span></span><br><span class="line">        </span><br><span class="line"><span class="number">3.</span>如何将单机变成连网</span><br><span class="line">    <span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">    假设上述是一个单机游戏</span></span><br><span class="line"><span class="string">    那么每个人的游戏记录只会在自己的计算机上面保存</span></span><br><span class="line"><span class="string">    注册登录的账号也只能在自己的计算机上有效 这样是不行的</span></span><br><span class="line"><span class="string">    所以我们可以</span></span><br><span class="line"><span class="string">    将数据库保存部分全部统一起来</span></span><br><span class="line"><span class="string">	所有人操作数据都来一个地方操作</span></span><br><span class="line"><span class="string">    &quot;&quot;&quot;</span></span><br></pre></td></tr></table></figure>

<h2 id="二、数据库的本质"><a href="#二、数据库的本质" class="headerlink" title="二、数据库的本质"></a>二、数据库的本质</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">本质其实就是一款基于网络通信的应用程序</span></span><br><span class="line"><span class="string">那其实每个人都可以开发一款数据库软件 因为它仅仅就是一个基于网络通信的应用程序</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">也就意味着数据库软件其实有很多很多</span></span><br><span class="line"><span class="string">	关系型数据库</span></span><br><span class="line"><span class="string">		MySQL、oracle、db2、access、sql server</span></span><br><span class="line"><span class="string">	非关系型数据库</span></span><br><span class="line"><span class="string">		redis、mongodb、memcache</span></span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="comment"># 关系型</span></span><br><span class="line">	<span class="number">1</span> 数据之间彼此有关系或者约束</span><br><span class="line">    	男生表和前女友表</span><br><span class="line">    <span class="number">2</span> 存储数据的表现形式通常是以表格存储	</span><br><span class="line">    	name     password		hobby</span><br><span class="line">        jason	  <span class="number">123</span>			学习</span><br><span class="line">        egon	  <span class="number">123</span>			女教练</span><br><span class="line">        tank	  <span class="number">123</span>			吃生蚝</span><br><span class="line">        每个字段还会有存储类型的限制</span><br><span class="line">        比如姓名只能存字符串...</span><br><span class="line"><span class="comment"># 非关系型</span></span><br><span class="line">	存储数据通常都是以k,v键值对的形式</span><br></pre></td></tr></table></figure>

<h2 id="三、MySQL"><a href="#三、MySQL" class="headerlink" title="三、MySQL"></a>三、MySQL</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">任何基于网络通信的应用程序底层用的都是socket</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">-服务端</span></span><br><span class="line"><span class="string">	-基于socket通信</span></span><br><span class="line"><span class="string">	-收发消息</span></span><br><span class="line"><span class="string">	-SQL语句</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">-客户端</span></span><br><span class="line"><span class="string">	-基于socket通信</span></span><br><span class="line"><span class="string">	-收发消息</span></span><br><span class="line"><span class="string">	-SQL语句</span></span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line">MySQL不单单支持MySQL自己的客户端app还支持其他编程语言来充当客户端操作</span><br><span class="line">	如何解决语言沟通的障碍？</span><br><span class="line">    	<span class="comment"># 1 让服务端兼容所有的语言(一个人精通多国语言)</span></span><br><span class="line">        <span class="comment"># 2 采用统一的语言(SQL语句)</span></span><br></pre></td></tr></table></figure>

<h2 id="四、重要概念介绍"><a href="#四、重要概念介绍" class="headerlink" title="四、重要概念介绍"></a>四、重要概念介绍</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">库 			》》》			 文件夹</span></span><br><span class="line"><span class="string">表			》》》			 文件</span></span><br><span class="line"><span class="string">记录		   》》》			文件内一行行的数据</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">    	name     password		hobby</span></span><br><span class="line"><span class="string">        jason	  123			学习</span></span><br><span class="line"><span class="string">        egon	  123			女教练</span></span><br><span class="line"><span class="string">        tank	  123			吃生蚝</span></span><br><span class="line"><span class="string">表头				表格的第一行字段</span></span><br><span class="line"><span class="string">字段				name、password、hobby</span></span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br></pre></td></tr></table></figure>

<h2 id="五、MySQL的安装"><a href="#五、MySQL的安装" class="headerlink" title="五、MySQL的安装"></a>五、MySQL的安装</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">在IT界 一般都不会轻易的使用最新版本的软件，因为新版本可能会出现各种问题</span></span><br><span class="line"><span class="string">(你原本项目跑的好好的 非要画蛇添足更新版本 然后项目奔溃)</span></span><br><span class="line"><span class="string">小段子:</span></span><br><span class="line"><span class="string">	更新完没事 那么你还是一个普通员工</span></span><br><span class="line"><span class="string">	更新完出事 那么你就是一名&quot;烈士&quot;</span></span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="comment"># MySQL有很多版本(5.6、5.7、8.0) 目前企业里面用的比较多的还是5.6左右</span></span><br></pre></td></tr></table></figure>

<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">参考网站:https://www.mysql.com/</span><br><span class="line"></span><br><span class="line">下载<span class="number">5.6</span>版本即可，如果你下载了其他版本问题也不大，因为sql语句是一样的学</span><br><span class="line"></span><br><span class="line">按照教学方式下载，会将服务端和客户端一并下载到本地</span><br><span class="line"></span><br><span class="line">为了学习方便我们将服务端和客户端都在本地启动，后期到了公司服务端会专门跑在一台服务器上，所有人基于网络连接服务端操作</span><br></pre></td></tr></table></figure>

<h2 id="六、MySQL服务端与客户端"><a href="#六、MySQL服务端与客户端" class="headerlink" title="六、MySQL服务端与客户端"></a>六、MySQL服务端与客户端</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># exe文件名</span></span><br><span class="line">    <span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">    服务端</span></span><br><span class="line"><span class="string">    mysqld.exe</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">    客户端</span></span><br><span class="line"><span class="string">    mysql.exe</span></span><br><span class="line"><span class="string">    &quot;&quot;&quot;</span></span><br></pre></td></tr></table></figure>

<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 注意</span></span><br><span class="line">    <span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">    在前期配置MySQL的时候 cmd终端尽量以管理员的身份运行</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">    windows+r 输入cmd  进入的是普通用户终端 有一些命令是无法执行的</span></span><br><span class="line"><span class="string">    搜索cmd右键 以管理员身份运行</span></span><br><span class="line"><span class="string">    &quot;&quot;&quot;</span></span><br></pre></td></tr></table></figure>

<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 启动</span></span><br><span class="line">    先切换到mysqld所在的<span class="built_in">bin</span>目录下，然后输入mysqld即可</span><br><span class="line">    保留原来的cmd窗口重新打开一个</span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">常见软件的默认端口号</span></span><br><span class="line"><span class="string">	MySQL  3306</span></span><br><span class="line"><span class="string">	redis  6379</span></span><br><span class="line"><span class="string">	mongodb 27017</span></span><br><span class="line"><span class="string">	django  8000</span></span><br><span class="line"><span class="string">	flask   5000</span></span><br><span class="line"><span class="string">	...</span></span><br><span class="line"><span class="string">	</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">MySQL第一次以管理员身份进入是没有密码的 直接回车即可</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">客户端连接服务端完整命令</span></span><br><span class="line"><span class="string">	mysql -h 127.0.0.1 -P 3306 -uroot -p</span></span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br></pre></td></tr></table></figure>

<h2 id="七、MySQL语句初识"><a href="#七、MySQL语句初识" class="headerlink" title="七、MySQL语句初识"></a>七、MySQL语句初识</h2><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">1. MySQL结束是以分号(；)为标志</span><br><span class="line">2. 基本命令</span><br><span class="line">	show databases  # 查看全部的库名</span><br><span class="line">3. MySQL连接精简版命令</span><br><span class="line">	mysql -uroot -p </span><br><span class="line">	# 省去了默认ip和端口</span><br><span class="line">4. 当你输入的命令不对的时候，不想让他继续执行和报错</span><br><span class="line">	输入\c即可(不用分号)</span><br><span class="line">5. 客户端退出</span><br><span class="line">	quit</span><br><span class="line">	exit</span><br><span class="line">	# 不用加分号</span><br><span class="line">6. 当你在连接服务端的时候，直接输入MySQL，就可以登录进去，但是是游客模式，只会给你展示一部分内容</span><br></pre></td></tr></table></figure>

<h2 id="八、环境变量-amp-系统服务制作"><a href="#八、环境变量-amp-系统服务制作" class="headerlink" title="八、环境变量&amp;系统服务制作"></a>八、环境变量&amp;系统服务制作</h2><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">&quot;&quot;&quot;</span><br><span class="line">	找到mysql的bin文件夹绝对路径，然后邮件我的电脑，点击属性，再点击高级系统设置，在然后点击环境变量，找到下面的path，双击，在新建一个把文件夹绝对路径输入进去，就可以直接cmd输入mysqld啦，不用切换路径文件夹了</span><br><span class="line">	我们环境变量弄好了，直接cmd输入mysqld --install 然后显示Service successfully installed就成功了，这时候我们在打开任务管理器，点击管理，找到mysql，右键点击开始，然后这个时候就被我们设置成系统开机就会自动启动的服务端啦，然后直接cmd 直接连接客户端就好啦</span><br><span class="line">&quot;&quot;&quot;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h6 id="小知识点补充——–如何查看当前进程-amp-如何杀死当前进程"><a href="#小知识点补充——–如何查看当前进程-amp-如何杀死当前进程" class="headerlink" title="***小知识点补充——–如何查看当前进程&amp;如何杀死当前进程"></a>***小知识点补充——–如何查看当前进程&amp;如何杀死当前进程</h6><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="string">&#x27;&#x27;&#x27;</span></span><br><span class="line"><span class="string">1.如何查看当前进程</span></span><br><span class="line"><span class="string">	tasklist |findstr mysql</span></span><br><span class="line"><span class="string">2.如何杀死进程</span></span><br><span class="line"><span class="string">	taskkill /F /PID PID号</span></span><br><span class="line"><span class="string">&#x27;&#x27;&#x27;</span></span><br></pre></td></tr></table></figure>

<h2 id="九、设置密码"><a href="#九、设置密码" class="headerlink" title="九、设置密码"></a>九、设置密码</h2><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">&quot;&quot;&quot;</span><br><span class="line">mysqladmin -uroot -p 原密码 password 新密码</span><br><span class="line">直接在cmd输入 不用打开mysql</span><br><span class="line">例如 mysqladmin -uroot -p 123 password 123456</span><br><span class="line">&quot;&quot;&quot;</span><br></pre></td></tr></table></figure>

<h2 id="十、重置密码"><a href="#十、重置密码" class="headerlink" title="十、重置密码"></a>十、重置密码</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">你可以将mysql获取用户名和密码校验的功能看成是一个装饰器</span></span><br><span class="line"><span class="string">装饰在了客户端请求访问的功能上</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">我们如果将该装饰器移除 那么mysql服务端就不会校验用户名和密码了</span></span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="comment"># 1 先关闭当前mysql服务端</span></span><br><span class="line"> 命令行的方式启动(让mysql跳过用户名密码验证功能)</span><br><span class="line"> mysqld --skip-grant-tables  </span><br><span class="line"><span class="comment"># 2 直接以无密码的方式连接</span></span><br><span class="line">	mysql -uroot -p   直接回车</span><br><span class="line"><span class="comment"># 3 修改当前用户的密码</span></span><br><span class="line">	update mysql.user <span class="built_in">set</span> password=password(<span class="number">123456</span>) where 		user=<span class="string">&#x27;root&#x27;</span> <span class="keyword">and</span> host=<span class="string">&#x27;localhost&#x27;</span>;</span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">真正存储用户表的密码字段 存储的肯定是密文 </span></span><br><span class="line"><span class="string">只有用户自己知道明文是什么 其他人都不知道 这样更加的安全</span></span><br><span class="line"><span class="string">密码比对也只能比对密文</span></span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="comment"># 4 立刻将修改数据刷到硬盘</span></span><br><span class="line">	flush privileges;</span><br><span class="line"><span class="comment"># 5 关闭当前服务端 然后以正常校验授权表的形式启动</span></span><br></pre></td></tr></table></figure>

<h2 id="十一、MySQL配置文件修改—–统一编码"><a href="#十一、MySQL配置文件修改—–统一编码" class="headerlink" title="十一、MySQL配置文件修改—–统一编码"></a>十一、MySQL配置文件修改—–统一编码</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">我们来打开my-default.ini的文件，ini为结尾的文件一般都是配置文件，程序启动会先加载配置文件在真正启动</span></span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line">[mysqld]  <span class="comment"># 一旦服务端启动立刻加载下面的配置</span></span><br><span class="line">sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES </span><br><span class="line">[mysql]  <span class="comment"># 一旦客户端启动立刻加载下面的配置</span></span><br><span class="line">...</span><br><span class="line">[client]  <span class="comment"># 其他客户端</span></span><br><span class="line">...</span><br><span class="line"><span class="comment"># 我们来新建一个ini文件------my.ini</span></span><br><span class="line"><span class="comment"># 我们在这里来写个同意编码的文件，然后我们还可以偷个懒，把用户名和密码也写进去，实现自动登录，代码如下</span></span><br><span class="line">    [mysqld]</span><br><span class="line">    character-<span class="built_in">set</span>-server=utf8</span><br><span class="line">    collation-server=utf8_general_ci</span><br><span class="line">    [client]</span><br><span class="line">    default-character-<span class="built_in">set</span>=utf8</span><br><span class="line">    [mysql]</span><br><span class="line">    user=<span class="string">&quot;root&quot;</span></span><br><span class="line">    password=<span class="number">123456</span></span><br><span class="line">    default-character-<span class="built_in">set</span>=utf8</span><br><span class="line"><span class="comment"># 我们修改后 要去重启服务端，然后再去登陆</span></span><br></pre></td></tr></table></figure>

<h2 id="十二、MySQL的基本语句"><a href="#十二、MySQL的基本语句" class="headerlink" title="十二、MySQL的基本语句"></a>十二、MySQL的基本语句</h2><h3 id="12-1-库的增删改查-文件夹"><a href="#12-1-库的增删改查-文件夹" class="headerlink" title="12.1 库的增删改查(文件夹)"></a>12.1 库的增删改查(文件夹)</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"># 增</span><br><span class="line">	create database ame;</span><br><span class="line">    create database yuki charset=&#x27;gbk&#x27;;</span><br><span class="line"># 查</span><br><span class="line">	show database;</span><br><span class="line">    show create database yuki;</span><br><span class="line"># 改</span><br><span class="line">	alter database ame charset=&#x27;gbk&#x27;;</span><br><span class="line"># 删</span><br><span class="line">	drop database ame;</span><br></pre></td></tr></table></figure>



<h3 id="12-2-表的增删改查-文件"><a href="#12-2-表的增删改查-文件" class="headerlink" title="12.2 表的增删改查(文件)"></a>12.2 表的增删改查(文件)</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line"># 查看当前库所在</span><br><span class="line">	select database();</span><br><span class="line"># 切换库</span><br><span class="line">	use ame;</span><br><span class="line"># 增</span><br><span class="line">	create table t1(id int, name char(4));  # 里面4的意思就是最大数据4个</span><br><span class="line"># 查</span><br><span class="line">	show tables  # 查看所有表</span><br><span class="line">	show create table t1  # 查看t1表</span><br><span class="line">	describe t1;  # 查看t1表 简写 desc t1</span><br><span class="line"># 改 </span><br><span class="line">	alter table t1 modify name char(16);</span><br><span class="line"># 删</span><br><span class="line">	drop table t1;</span><br><span class="line">&quot;&quot;&quot;</span><br><span class="line">create table ame.t1(id int, name char(16));  使用绝对路径也是可以增加的，用来操做不同的库</span><br><span class="line">&quot;&quot;&quot;</span><br></pre></td></tr></table></figure>

<h3 id="12-3-数据的增删改查"><a href="#12-3-数据的增删改查" class="headerlink" title="12.3 数据的增删改查"></a>12.3 数据的增删改查</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"># 增</span><br><span class="line">	insert into t1 values(1, &#x27;Ame&#x27;);</span><br><span class="line">	insert into t1 values(2, &#x27;yuki&#x27;),(3, &#x27;ori&#x27;);</span><br><span class="line"># 查</span><br><span class="line">	select * from t1;  # 查看所有 数据特别大不建议用</span><br><span class="line">	select name from t1;  # 查看name</span><br><span class="line"># 改</span><br><span class="line">	update t1 set name=&#x27;DSB&#x27; where id &gt; 1;</span><br><span class="line"># 删</span><br><span class="line">	delete from t1 where id &gt; 1;</span><br><span class="line">	delete from t1 where name=&#x27;Ame&#x27;;</span><br><span class="line"># 将表所有的数据清空</span><br><span class="line">	delete from t1;</span><br></pre></td></tr></table></figure>

<h2 id="十三、存储引擎"><a href="#十三、存储引擎" class="headerlink" title="十三、存储引擎"></a>十三、存储引擎</h2><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br></pre></td><td class="code"><pre><span class="line">&quot;&quot;&quot;</span><br><span class="line">MySQL 主要的存储引擎</span><br><span class="line">	1.Innodb</span><br><span class="line">    	是MySQL5.5版本机制后默认的存储引擎</span><br><span class="line">    	存储数据更加的安全</span><br><span class="line">    2. myisam</span><br><span class="line">    	是MySQL5.5版本之前默认的存储引擎</span><br><span class="line">    	速度要比Innodb更快 但是我们更加注重的是数据的安全</span><br><span class="line">    3.memory</span><br><span class="line">    	内存引擎(数据全部存放在内存中)断电数据丢失</span><br><span class="line">    4.blackhole</span><br><span class="line">		无论存什么，都立刻消失(黑洞)</span><br><span class="line">&quot;&quot;&quot;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">&quot;&quot;&quot;</span><br><span class="line"># 查看所有的存储引擎</span><br><span class="line">	show engines;</span><br><span class="line"># 不同的存储引擎在存储表的时候 异同点</span><br><span class="line">    create table t1(id int) engine=innodb;</span><br><span class="line">    create table t2(id int) engine=myisam;</span><br><span class="line">    create table t3(id int) engine=blackhole;</span><br><span class="line">    create table t4(id int) engine=memory;</span><br><span class="line"># 存数据</span><br><span class="line">	insert into t1 values(1);</span><br><span class="line">	insert into t2 values(1);</span><br><span class="line">	insert into t3 values(1);</span><br><span class="line">	insert into t4 values(1);</span><br><span class="line">&quot;&quot;&quot;</span><br></pre></td></tr></table></figure>

<h2 id="十四、创建表的完整语法"><a href="#十四、创建表的完整语法" class="headerlink" title="十四、创建表的完整语法"></a>十四、创建表的完整语法</h2><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br></pre></td><td class="code"><pre><span class="line"># 语法</span><br><span class="line">create table 表名(</span><br><span class="line">	字段名1 类型(宽度) 约束条件,</span><br><span class="line">    字段名2 类型(宽度) 约束条件,</span><br><span class="line">    字段名3 类型(宽度) 约束条件</span><br><span class="line">)</span><br><span class="line"></span><br><span class="line"># 注意</span><br><span class="line">1 在同一张表中字段名不能重复</span><br><span class="line">2 宽度和约束条件是可选的(可写可不写) 而字段名和字段类型是必须的</span><br><span class="line">	约束条件写的话 也支持写多个</span><br><span class="line">    字段名1 类型(宽度) 约束条件1 约束条件2...,</span><br><span class="line">	create table t5(id);  报错</span><br><span class="line">3 最后一行不能有逗号</span><br><span class="line">	create table t6(</span><br><span class="line">        id int,</span><br><span class="line">        name char,</span><br><span class="line">    );   报错</span><br><span class="line"></span><br><span class="line">&quot;&quot;&quot;补充&quot;&quot;&quot;</span><br><span class="line"># 宽度</span><br><span class="line">	一般情况下指的是对存储数据的限制</span><br><span class="line">	create table t7(name char);  默认宽度是1</span><br><span class="line">    insert into t7 values(&#x27;jason&#x27;);</span><br><span class="line">    insert into t7 values(null);  关键字NULL</span><br><span class="line">    	针对不同的版本会出现不同的效果</span><br><span class="line">        	5.6版本默认没有开启严格模式 规定只能存一个字符你给了多个字符，那么我会自动帮你截取</span><br><span class="line">            5.7版本及以上或者开启了严格模式 那么规定只能存几个 就不能超，一旦超出范围立刻报错 Data too long for ....</span><br><span class="line">&quot;&quot;&quot;严格模式到底开不开呢？&quot;&quot;&quot;</span><br><span class="line">MySQL5.7之后的版本默认都是开启严格模式的</span><br><span class="line">使用数据库的准则:</span><br><span class="line">	能尽量少的让数据库干活就尽量少 不要给数据库增加额外的压力</span><br><span class="line"></span><br><span class="line"># 约束条件 null  not null不能插入null</span><br><span class="line">create table t8(id int,name char not null);</span><br><span class="line"></span><br><span class="line">&quot;&quot;&quot;</span><br><span class="line">宽度和约束条件到底是什么关系</span><br><span class="line">	宽度是用来限制数据的存储</span><br><span class="line">	约束条件是在宽度的基础之上增加的额外的约束</span><br><span class="line">&quot;&quot;&quot;</span><br></pre></td></tr></table></figure>

<h2 id="十五、基本数据类型"><a href="#十五、基本数据类型" class="headerlink" title="十五、基本数据类型"></a>十五、基本数据类型</h2><h3 id="15-1-整形"><a href="#15-1-整形" class="headerlink" title="15.1 整形"></a>15.1 整形</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br></pre></td><td class="code"><pre><span class="line">1.分类 </span><br><span class="line">	tinyint smallint meduimint int bigint</span><br><span class="line">2.作用</span><br><span class="line">	存储年龄 等级 id 号码...</span><br><span class="line">&quot;&quot;&quot;</span><br><span class="line">以tinyint</span><br><span class="line">	是否有符号</span><br><span class="line">		默认情况下是带符号的</span><br><span class="line">	超出会如何</span><br><span class="line">		超出限制只存最大可接受值</span><br><span class="line">&quot;&quot;&quot;</span><br><span class="line">create table t9(id tinyint);</span><br><span class="line">insert into t9 values(-129),(256);</span><br><span class="line"></span><br><span class="line"># 约束条件之unsigned 无符号</span><br><span class="line">create table t10(id tinyint unsigned);</span><br><span class="line"></span><br><span class="line"># int默认也是带符号的</span><br><span class="line"># 整形默认情况下都是带有符号的</span><br><span class="line">create table t11(id int);</span><br><span class="line"></span><br><span class="line"># 针对整形 括号内的宽度到底是干嘛的</span><br><span class="line">create table t12(id int(8));</span><br><span class="line">insert into t12 values(123456789)</span><br><span class="line">&quot;&quot;&quot;</span><br><span class="line">特例：只有整形括号里面的数字不是表示限制位数</span><br><span class="line">id int(8)</span><br><span class="line">	如果数字没有超出8位 那么默认用空格填充至8位</span><br><span class="line">	如果数字超出了8位 那么有几位就存几位(但是还要是遵守最大范围)</span><br><span class="line">&quot;&quot;&quot;</span><br><span class="line">create table t13(id int(8) unsigned zerofill);</span><br><span class="line"># 用0填充到8位</span><br><span class="line"># 总结</span><br><span class="line">	针对整形字段 括号内无需指定宽度 因为他默认的宽度以及足够显示所有的数据了</span><br></pre></td></tr></table></figure>

<h3 id="15-2-严格模式-小插曲"><a href="#15-2-严格模式-小插曲" class="headerlink" title="15.2 严格模式(小插曲)"></a>15.2 严格模式(小插曲)</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 如何查看严格模式</span></span><br><span class="line">show variables like <span class="string">&quot;%mode&quot;</span>;</span><br><span class="line"></span><br><span class="line">模糊匹配/查询</span><br><span class="line">	关键字 like</span><br><span class="line">		%:匹配任意多个字符</span><br><span class="line">        _:匹配任意单个字符</span><br><span class="line"></span><br><span class="line"><span class="comment"># 修改严格模式</span></span><br><span class="line">	<span class="built_in">set</span> session  只在当前窗口有效</span><br><span class="line">    <span class="built_in">set</span> <span class="keyword">global</span>   全局有效</span><br><span class="line">    </span><br><span class="line">    <span class="built_in">set</span> <span class="keyword">global</span> sql_mode = <span class="string">&#x27;STRICT_TRANS_TABLES&#x27;</span>;</span><br><span class="line">    </span><br><span class="line">    修改完之后 重新进入服务端即可</span><br></pre></td></tr></table></figure>

<h3 id="15-3-浮点型"><a href="#15-3-浮点型" class="headerlink" title="15.3 浮点型"></a>15.3 浮点型</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line"><span class="number">1.</span>分类</span><br><span class="line">	FLOAT、DOUBLE、DECIMAL</span><br><span class="line"></span><br><span class="line"><span class="number">2.</span>作用</span><br><span class="line">	身高、体重、薪资</span><br><span class="line"><span class="comment"># 存储限制</span></span><br><span class="line"><span class="built_in">float</span>(<span class="number">255</span>,<span class="number">30</span>)  <span class="comment"># 总共255位 小数部分占30位</span></span><br><span class="line">double(<span class="number">255</span>,<span class="number">30</span>)  <span class="comment"># 总共255位 小数部分占30位</span></span><br><span class="line">decimal(<span class="number">65</span>,<span class="number">30</span>)  <span class="comment"># 总共65位 小数部分占30位</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 精确度验证</span></span><br><span class="line">create table t15(<span class="built_in">id</span> <span class="built_in">float</span>(<span class="number">255</span>,<span class="number">30</span>));</span><br><span class="line">create table t16(<span class="built_in">id</span> double(<span class="number">255</span>,<span class="number">30</span>));</span><br><span class="line">create table t17(<span class="built_in">id</span> decimal(<span class="number">65</span>,<span class="number">30</span>));</span><br><span class="line"><span class="string">&quot;&quot;&quot;你们在前期不要给我用反向键 所有的命令全部手敲！！！增加熟练度&quot;&quot;&quot;</span></span><br><span class="line"></span><br><span class="line">insert into t15 values(<span class="number">1.111111111111111111111111111111</span>);</span><br><span class="line">insert into t16 values(<span class="number">1.111111111111111111111111111111</span>);</span><br><span class="line">insert into t17 values(<span class="number">1.111111111111111111111111111111</span>);</span><br><span class="line"></span><br><span class="line"><span class="built_in">float</span> &lt; double &lt; decimal</span><br><span class="line"><span class="comment"># 要结合实际应用场景 三者都能使用</span></span><br></pre></td></tr></table></figure>

<h3 id="15-4-字符类型"><a href="#15-4-字符类型" class="headerlink" title="15.4 字符类型"></a>15.4 字符类型</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br></pre></td><td class="code"><pre><span class="line">1.分类</span><br><span class="line">   &quot;&quot;&quot;</span><br><span class="line">    char</span><br><span class="line">        定长</span><br><span class="line">        char(4)	 数据超过四个字符直接报错 不够四个字符空格补全</span><br><span class="line">    varchar</span><br><span class="line">        变长</span><br><span class="line">        varchar(4)  数据超过四个字符直接报错 不够有几个存几个</span><br><span class="line">    &quot;&quot;&quot;</span><br><span class="line">    create table t18(name char(4));</span><br><span class="line">    create table t19(name varchar(4));</span><br><span class="line"></span><br><span class="line">    insert into t18 values(&#x27;a&#x27;);</span><br><span class="line">    insert into t19 values(&#x27;a&#x27;);</span><br><span class="line"></span><br><span class="line"># 介绍一个小方法 char_length统计字段长度</span><br><span class="line">    select char_length(name) from t18;</span><br><span class="line">    select char_length(name) from t19;</span><br><span class="line">    &quot;&quot;&quot;</span><br><span class="line">    首先可以肯定的是 char硬盘上存的绝对是真正的数据 带有空格的</span><br><span class="line">    但是在显示的时候MySQL会自动将多余的空格剔除</span><br><span class="line">    &quot;&quot;&quot;</span><br><span class="line"></span><br><span class="line"># 再次修改sql_mode 让MySQL不要做自动剔除操作</span><br><span class="line">	set global sql_mode = &#x27;STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH&#x27;;	</span><br><span class="line"></span><br><span class="line"># char 与 varchar对比</span><br><span class="line">    &quot;&quot;&quot;</span><br><span class="line">    char</span><br><span class="line">        缺点:浪费空间</span><br><span class="line">        优点:存取都很简单</span><br><span class="line">            直接按照固定的字符存取数据即可</span><br><span class="line">            jason egon alex wusir tank </span><br><span class="line">            存按照五个字符存 取也直接按照五个字符取</span><br><span class="line"></span><br><span class="line">    varchar</span><br><span class="line">        优点:节省空间</span><br><span class="line">        缺点:存取较为麻烦</span><br><span class="line">            1bytes+jason 1bytes+egon 1bytes+alex 1bytes+tank </span><br><span class="line"></span><br><span class="line">            存的时候需要制作报头</span><br><span class="line">            取的时候也需要先读取报头 之后才能读取真实数据</span><br><span class="line"></span><br><span class="line">    以前基本上都是用的char 其实现在用varchar的也挺多</span><br><span class="line">    &quot;&quot;&quot;</span><br><span class="line"></span><br><span class="line">补充:</span><br><span class="line">    进来公司之后你完全不需要考虑字段类型和字段名</span><br><span class="line">    因为产品经理给你发的邮件上已经全部指明了</span><br></pre></td></tr></table></figure>

<h3 id="15-5-时间类型"><a href="#15-5-时间类型" class="headerlink" title="15.5 时间类型"></a>15.5 时间类型</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">1.分类</span><br><span class="line">    date:年月日 2020-5-4</span><br><span class="line">    datetime:年月日时分秒  2020-5-4 11:11:11</span><br><span class="line">    time:时分秒 11:11:11</span><br><span class="line">    Year:年 2020</span><br><span class="line">    </span><br><span class="line">2.具体使用</span><br><span class="line">	create table student(</span><br><span class="line">        id int,</span><br><span class="line">        name varchar(16),</span><br><span class="line">        born_year year,</span><br><span class="line">        birth date,</span><br><span class="line">        study_time time,</span><br><span class="line">        reg_time datetime</span><br><span class="line">    );</span><br><span class="line">    insert into student values(1,&#x27;egon&#x27;,&#x27;1880&#x27;,&#x27;1880-11-11&#x27;,&#x27;11:11:11&#x27;,&#x27;2020-11-11 11:11:11&#x27;);</span><br></pre></td></tr></table></figure>

<h3 id="15-6-枚举与集合类型"><a href="#15-6-枚举与集合类型" class="headerlink" title="15.6 枚举与集合类型"></a>15.6 枚举与集合类型</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br></pre></td><td class="code"><pre><span class="line">1.分类</span><br><span class="line">    &quot;&quot;&quot;</span><br><span class="line">    枚举(enum)  多选一</span><br><span class="line">    集合(set)   多选多</span><br><span class="line">    &quot;&quot;&quot;	</span><br><span class="line">2.具体使用</span><br><span class="line">    create table user(</span><br><span class="line">        id int,</span><br><span class="line">        name char(16),</span><br><span class="line">        gender enum(&#x27;male&#x27;,&#x27;female&#x27;,&#x27;others&#x27;)</span><br><span class="line">    );</span><br><span class="line">    insert into user values(1,&#x27;jason&#x27;,&#x27;male&#x27;);  正常</span><br><span class="line">    insert into user values(2,&#x27;egon&#x27;,&#x27;xxxxooo&#x27;);  报错</span><br><span class="line"># 枚举字段 后期在存数据的时候只能从枚举里面选择一个存储 </span><br><span class="line">    create table teacher(</span><br><span class="line">        id int,</span><br><span class="line">        name char(16),</span><br><span class="line">        gender enum(&#x27;male&#x27;,&#x27;female&#x27;,&#x27;others&#x27;),</span><br><span class="line">        hobby set(&#x27;read&#x27;,&#x27;DBJ&#x27;,&#x27;hecha&#x27;)</span><br><span class="line">    );</span><br><span class="line">    insert into teacher values(1,&#x27;jason&#x27;,&#x27;male&#x27;,&#x27;read&#x27;);  正常</span><br><span class="line">    insert into teacher values(2,&#x27;egon&#x27;,&#x27;female&#x27;,&#x27;DBJ,hecha&#x27;);  正常</span><br><span class="line">    insert into teacher values(3,&#x27;tank&#x27;,&#x27;others&#x27;,&#x27;生蚝&#x27;); 报错</span><br><span class="line"># 集合可以只写一个  但是不能写没有列举的</span><br></pre></td></tr></table></figure>

<h2 id="十六、约束条件"><a href="#十六、约束条件" class="headerlink" title="十六、约束条件"></a>十六、约束条件</h2><h3 id="16-1-default-默认值"><a href="#16-1-default-默认值" class="headerlink" title="16.1 default 默认值"></a>16.1 default 默认值</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">default 默认值</span><br><span class="line">	补充知识点 插入数据的时候可以指定字段</span><br><span class="line">	create table t1(</span><br><span class="line">    	id int,</span><br><span class="line">    	name char(16)</span><br><span class="line">    );</span><br><span class="line">    insert into t1(name,id) values(&#x27;ame&#x27;,1)</span><br><span class="line">    </span><br><span class="line">    create table t2(</span><br><span class="line">    	id int,</span><br><span class="line">    	name char(16),</span><br><span class="line">    	gender enum(&#x27;male&#x27;,&#x27;female&#x27;,&#x27;others&#x27;)default &#x27;male&#x27;</span><br><span class="line">    );</span><br><span class="line">    insert into t2(id, name) values(1,&#x27;jason&#x27;);</span><br><span class="line">    insert into t2 values(2, &#x27;egon&#x27;, &#x27;female&#x27;);</span><br><span class="line">    </span><br></pre></td></tr></table></figure>

<h3 id="16-2-unique唯一"><a href="#16-2-unique唯一" class="headerlink" title="16.2 unique唯一"></a>16.2 unique唯一</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br></pre></td><td class="code"><pre><span class="line">unique唯一</span><br><span class="line">    # 单列唯一</span><br><span class="line">    create table t3(</span><br><span class="line">        id int unique,</span><br><span class="line">        name char(16)</span><br><span class="line">    );</span><br><span class="line">    insert into t3 values(1,&#x27;jason&#x27;),(1,&#x27;egon&#x27;);</span><br><span class="line">    insert into t3 values(1,&#x27;jason&#x27;),(2,&#x27;egon&#x27;);</span><br><span class="line">    </span><br><span class="line">    # 联合唯一</span><br><span class="line">    &quot;&quot;&quot;</span><br><span class="line">    ip和port</span><br><span class="line">    单个都可以重复 但是加在一起必须是唯一的</span><br><span class="line">    &quot;&quot;&quot;</span><br><span class="line">    create table t4(</span><br><span class="line">    	id int,</span><br><span class="line">    	ip char(16),</span><br><span class="line">    	port int,</span><br><span class="line">    	unique(ip,port)</span><br><span class="line">    );</span><br><span class="line">    insert into t4 values(1,&#x27;127.0.0.1&#x27;,8080);</span><br><span class="line">    insert into t4 values(2,&#x27;127.0.0.1&#x27;,8081);</span><br><span class="line">    insert into t4 values(3,&#x27;127.0.0.2&#x27;,8080);</span><br><span class="line">    insert into t4 values(4,&#x27;127.0.0.1&#x27;,8080);  报错</span><br></pre></td></tr></table></figure>

<h3 id="16-3-primary-key-主键"><a href="#16-3-primary-key-主键" class="headerlink" title="16.3 primary key 主键"></a>16.3 primary key 主键</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br></pre></td><td class="code"><pre><span class="line">&quot;&quot;&quot;</span><br><span class="line">1.单单从约束效果上来看primary key等价于not null + unique</span><br><span class="line">非空且唯一!!!</span><br><span class="line">&quot;&quot;&quot;</span><br><span class="line">    create table t5(id int primary key);</span><br><span class="line">    insert into t5 values(null); 报错</span><br><span class="line">    insert into t5 values(1),(1); 报错</span><br><span class="line">    insert into t5 values(1),(2);</span><br><span class="line"></span><br><span class="line">&quot;&quot;&quot;</span><br><span class="line">2.它除了有约束效果之外 他还是Innodb存储引擎组织数据的依据</span><br><span class="line">Innodb存储引擎在创建表的时候必须要有primary key</span><br><span class="line">因为它类似于书的目录 能够帮助提示查询效率并且也是建表的依据</span><br><span class="line">&quot;&quot;&quot;</span><br><span class="line">	2.1 一张表中有且只有一个主键 如果你没有设置主键 那么会从上往下搜索直到遇到一个非空且唯一的字段将他自动升级为主键</span><br><span class="line">        create table t6(</span><br><span class="line">            id int,</span><br><span class="line">            name char(16),</span><br><span class="line">            age int not null unique,</span><br><span class="line">            addr char(32) not null unique</span><br><span class="line">        );</span><br><span class="line">    2.2 如果表中没有主键也没有其他任何和的非空且唯一的字段 那么Innodb会采用自己内部提供的一个隐藏字段作为主键，隐藏意味着你无法使用到它 就无法提示查询速度</span><br><span class="line">    </span><br><span class="line">    3.3 一张表中通常都应该有一个主键字段 并且通常将id/uid/sid字段作为主键</span><br><span class="line">    	单个字段主键</span><br><span class="line">            create table t5(</span><br><span class="line">                id int primary key</span><br><span class="line">                name char(16)</span><br><span class="line">            );</span><br><span class="line">    	联合主键(多个字段联合起来作为表的主键 本质还是一个主键) </span><br><span class="line">            create table t7(</span><br><span class="line">                    ip char(16),</span><br><span class="line">                	port int,</span><br><span class="line">                    primary key(ip, port)</span><br><span class="line">                );</span><br><span class="line">    &quot;&quot;&quot;</span><br><span class="line">    也意味着 以后我们在创建表的时候id字段一定要加primary key</span><br><span class="line">    &quot;&quot;&quot;</span><br></pre></td></tr></table></figure>

<h3 id="16-4-auto-increment-自增"><a href="#16-4-auto-increment-自增" class="headerlink" title="16.4 auto_increment 自增"></a>16.4 auto_increment 自增</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"># 当编号特别多的时候 人为的去维护太麻烦</span><br><span class="line">    create table t8(</span><br><span class="line">        id int primary key auto_increment,</span><br><span class="line">        name char(16)</span><br><span class="line">    );</span><br><span class="line">    insert into t8(name) values(&#x27;jason&#x27;),(&#x27;egon&#x27;),(&#x27;kevin&#x27;);</span><br><span class="line"></span><br><span class="line"># 注意auto_increment通常都是加在主键上的 不能给普通字段加</span><br><span class="line">    create table t9(</span><br><span class="line">        id int primary key auto_increment,</span><br><span class="line">        name char(16),</span><br><span class="line">        cid int auto_increment</span><br><span class="line">    );</span><br><span class="line">    ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a 	key</span><br></pre></td></tr></table></figure>

<h2 id="十七、表与表之间建关系"><a href="#十七、表与表之间建关系" class="headerlink" title="十七、表与表之间建关系"></a>十七、表与表之间建关系</h2><h3 id="17-1-问题引出"><a href="#17-1-问题引出" class="headerlink" title="17.1 问题引出"></a>17.1 问题引出</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">&quot;&quot;&quot;</span><br><span class="line">定义一张员工表 表中有很多字段</span><br><span class="line">id name gender dep_name dep_desc</span><br><span class="line">&quot;&quot;&quot;</span><br><span class="line"># 1 该表的组织结构不是很清晰(可忽视)</span><br><span class="line"># 2 浪费硬盘空间(可忽视)</span><br><span class="line"># 3 数据的扩展性极差(无法忽视的)</span><br><span class="line"></span><br><span class="line"># 如何优化？</span><br><span class="line">&quot;&quot;&quot;上述问题就类似于你将所有的代码都写在了一个py文件中&quot;&quot;&quot;</span><br><span class="line">将员工表拆分  员工表和部门表</span><br></pre></td></tr></table></figure>

<h3 id="17-2-外键"><a href="#17-2-外键" class="headerlink" title="17.2 外键"></a>17.2 外键</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">&quot;&quot;&quot;</span><br><span class="line">外键就是用来帮助我们建立表与表之间关系的</span><br><span class="line">foreign key</span><br><span class="line">&quot;&quot;&quot;</span><br></pre></td></tr></table></figure>

<h3 id="17-3-表关系"><a href="#17-3-表关系" class="headerlink" title="17.3 表关系"></a>17.3 表关系</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">&quot;&quot;&quot;</span><br><span class="line">表与表之间最多只有四种关系</span><br><span class="line">	一对多关系</span><br><span class="line">		在MySQL的关系中没有多对一一说</span><br><span class="line">		一对多 多对一 都叫一对多！！！</span><br><span class="line">	多对多关系</span><br><span class="line">	一对一关系</span><br><span class="line">	没有关系</span><br><span class="line">&quot;&quot;&quot;</span><br></pre></td></tr></table></figure>

<h3 id="17-4-一对多关系"><a href="#17-4-一对多关系" class="headerlink" title="17.4 一对多关系"></a>17.4 一对多关系</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br></pre></td><td class="code"><pre><span class="line">&quot;&quot;&quot;</span><br><span class="line">判断表与表之间关系的时候 前期不熟悉的情况下 一定要按照我给你的建议</span><br><span class="line">换位思考  分别站在两张表的角度考虑</span><br><span class="line"></span><br><span class="line">员工表与部门表为例</span><br><span class="line">	先站在员工表</span><br><span class="line">		思考一个员工能否对应多个部门(一条员工数据能否对应多条部门数据)</span><br><span class="line">			不能！！！</span><br><span class="line">			(不能直接得出结论 一定要两张表都考虑完全)</span><br><span class="line">	再站在部门表</span><br><span class="line">		思考一个部门能否对应多个员工(一个部门数据能否对应多条员工数据)</span><br><span class="line">			能！！！</span><br><span class="line">	得出结论</span><br><span class="line">		员工表与部门表示单向的一对多</span><br><span class="line">		所以表关系就是一对多</span><br><span class="line">&quot;&quot;&quot;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">foreign key</span><br><span class="line">	1 一对多表关系   外键字段建在多的一方</span><br><span class="line">    2 在创建表的时候 一定要先建被关联表 </span><br><span class="line">    3 在录入数据的时候 也必须先录入被关联表</span><br><span class="line"># SQL语句建立表关系</span><br><span class="line">create table dep(</span><br><span class="line">	id int primary key auto_increment,</span><br><span class="line">    dep_name char(16),</span><br><span class="line">    dep_desc char(32)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line">create table emp(</span><br><span class="line">	id int primary key auto_increment,</span><br><span class="line">    name char(16),</span><br><span class="line">    gender enum(&#x27;male&#x27;,&#x27;female&#x27;,&#x27;others&#x27;) default &#x27;male&#x27;,</span><br><span class="line">    dep_id int,</span><br><span class="line">    foreign key(dep_id) references dep(id)</span><br><span class="line">);</span><br><span class="line">insert into dep(dep_name,dep_desc) values(&#x27;教学部&#x27;,&#x27;教书育人&#x27;),(&#x27;外交部&#x27;,&#x27;多人外交&#x27;),(&#x27;nb技术部&#x27;,&#x27;技术能力有限部门&#x27;);</span><br><span class="line">insert into emp(name,dep_id) values(&#x27;jason&#x27;,2),(&#x27;egon&#x27;,1),(&#x27;tank&#x27;,1),(&#x27;kevin&#x27;,3);</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"># 修改dep表里面的id字段</span><br><span class="line">update dep set id=200 where id=2;  不行</span><br><span class="line"># 删除dep表里面的数据</span><br><span class="line">delete from dep;  不行</span><br><span class="line"></span><br><span class="line"># 1 先删除教学部对应的员工数据 之后再删除部门</span><br><span class="line">	操作太过繁琐</span><br><span class="line">    </span><br><span class="line"># 2 真正做到数据之间有关系</span><br><span class="line">	更新就同步更新</span><br><span class="line">    删除就同步删除</span><br><span class="line">&quot;&quot;&quot;</span><br><span class="line">级联更新   &gt;&gt;&gt;   同步更新</span><br><span class="line">级联删除   &gt;&gt;&gt;   同步删除</span><br><span class="line">&quot;&quot;&quot;</span><br><span class="line">create table dep(</span><br><span class="line">	id int primary key auto_increment,</span><br><span class="line">    dep_name char(16),</span><br><span class="line">    dep_desc char(32)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line">create table emp(</span><br><span class="line">	id int primary key auto_increment,</span><br><span class="line">    name char(16),</span><br><span class="line">    gender enum(&#x27;male&#x27;,&#x27;female&#x27;,&#x27;others&#x27;) default &#x27;male&#x27;,</span><br><span class="line">    dep_id int,</span><br><span class="line">    foreign key(dep_id) references dep(id) </span><br><span class="line">    on update cascade  # 同步更新</span><br><span class="line">    on delete cascade  # 同步删除</span><br><span class="line">);</span><br><span class="line">insert into dep(dep_name,dep_desc) values(&#x27;sb教学部&#x27;,&#x27;教书育人&#x27;),(&#x27;外交部&#x27;,&#x27;多人外交&#x27;),(&#x27;nb技术部&#x27;,&#x27;技术能力有限部门&#x27;);</span><br><span class="line">insert into emp(name,dep_id) values(&#x27;jason&#x27;,2),(&#x27;egon&#x27;,1),(&#x27;tank&#x27;,1),(&#x27;kevin&#x27;,3);</span><br></pre></td></tr></table></figure>

<h3 id="17-5-多对多表关系"><a href="#17-5-多对多表关系" class="headerlink" title="17.5 多对多表关系"></a>17.5 多对多表关系</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br></pre></td><td class="code"><pre><span class="line">&quot;&quot;&quot;</span><br><span class="line">图书表和作者表</span><br><span class="line">&quot;&quot;&quot;</span><br><span class="line">create table book(</span><br><span class="line">	id int primary key auto_increment,</span><br><span class="line">    title varchar(32),</span><br><span class="line">    price int,</span><br><span class="line">    author_id int,</span><br><span class="line">    foreign key(author_id) references author(id) </span><br><span class="line">    on update cascade  # 同步更新</span><br><span class="line">    on delete cascade  # 同步删除</span><br><span class="line">);</span><br><span class="line">create table author(</span><br><span class="line">	id int primary key auto_increment,</span><br><span class="line">    name varchar(32),</span><br><span class="line">    age int,</span><br><span class="line">    book_id int,</span><br><span class="line">    foreign key(book_id) references book(id) </span><br><span class="line">    on update cascade  # 同步更新</span><br><span class="line">    on delete cascade  # 同步删除</span><br><span class="line">);</span><br><span class="line">&quot;&quot;&quot;</span><br><span class="line">按照上述的方式创建 一个都别想成功！！！</span><br><span class="line">其实我们只是想记录书籍和作者的关系</span><br><span class="line">针对多对多字段表关系 不能在两张原有的表中创建外键</span><br><span class="line">需要你单独再开设一张 专门用来存储两张表数据之间的关系</span><br><span class="line">&quot;&quot;&quot;</span><br><span class="line">create table book(</span><br><span class="line">	id int primary key auto_increment,</span><br><span class="line">    title varchar(32),</span><br><span class="line">    price int</span><br><span class="line">);</span><br><span class="line">create table author(</span><br><span class="line">	id int primary key auto_increment,</span><br><span class="line">    name varchar(32),</span><br><span class="line">    age int</span><br><span class="line">);</span><br><span class="line">create table book2author(</span><br><span class="line">	id int primary key auto_increment,</span><br><span class="line">    author_id int,</span><br><span class="line">    book_id int,</span><br><span class="line">    foreign key(author_id) references author(id) </span><br><span class="line">    on update cascade  # 同步更新</span><br><span class="line">    on delete cascade,  # 同步删除</span><br><span class="line">    foreign key(book_id) references book(id) </span><br><span class="line">    on update cascade  # 同步更新</span><br><span class="line">    on delete cascade  # 同步删除</span><br><span class="line">);</span><br></pre></td></tr></table></figure>

<h3 id="17-6-一对一"><a href="#17-6-一对一" class="headerlink" title="17.6 一对一"></a>17.6 一对一</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br></pre></td><td class="code"><pre><span class="line">&quot;&quot;&quot;</span><br><span class="line">id name age addr phone hobby email........</span><br><span class="line">如果一个表的字段特别多 每次查询又不是所有的字段都能用得到</span><br><span class="line">将表一分为二  </span><br><span class="line">	用户表</span><br><span class="line">		用户表</span><br><span class="line">			id name age</span><br><span class="line">		用户详情表</span><br><span class="line">			id addr phone hobby email........</span><br><span class="line">	</span><br><span class="line">	站在用户表</span><br><span class="line">		一个用户能否对应多个用户详情   不能！！！</span><br><span class="line">	站在详情表</span><br><span class="line">		一个详情能否属于多个用户      不能！！！</span><br><span class="line">	结论:单向的一对多都不成立 那么这个时候两者之间的表关系</span><br><span class="line">		就是一对一</span><br><span class="line">		或者没有关系(好判断)</span><br><span class="line"></span><br><span class="line">客户表和学生表</span><br><span class="line">	在你们报名之前你们是客户端</span><br><span class="line">	报名之后是学生(期间有一些客户不会报名)</span><br><span class="line">&quot;&quot;&quot;</span><br><span class="line"></span><br><span class="line">一对一 外键字段建在任意一方都可以 但是推荐你建在查询频率比较高的表中</span><br><span class="line">create table authordetail(</span><br><span class="line">	id int primary key auto_increment,</span><br><span class="line">    phone int,</span><br><span class="line">    addr varchar(64)</span><br><span class="line">);</span><br><span class="line">create table author(</span><br><span class="line">	id int primary key auto_increment,</span><br><span class="line">    name varchar(32),</span><br><span class="line">    age int,</span><br><span class="line">    authordetail_id int unique,</span><br><span class="line">    foreign key(authordetail_id) references authordetail(id) </span><br><span class="line">    on update cascade  # 同步更新</span><br><span class="line">    on delete cascade  # 同步删除</span><br><span class="line">)</span><br></pre></td></tr></table></figure>

<h3 id="17-7-总结"><a href="#17-7-总结" class="headerlink" title="17.7 总结"></a>17.7 总结</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line">&quot;&quot;&quot;</span><br><span class="line">表关系的建立需要用到foreign key</span><br><span class="line">	一对多</span><br><span class="line">		外键字段建在多的一方</span><br><span class="line">	多对多</span><br><span class="line">		自己开设第三张存储</span><br><span class="line">	一对一</span><br><span class="line">		建在任意一方都可以 但是推荐你建在查询频率较高的表中</span><br><span class="line"></span><br><span class="line">判断表之间关系的方式</span><br><span class="line">	换位思考！！！</span><br><span class="line">		员工与部门</span><br><span class="line">	</span><br><span class="line">		图书与作者</span><br><span class="line">	</span><br><span class="line">		作者与作者详情</span><br><span class="line">&quot;&quot;&quot;</span><br></pre></td></tr></table></figure>

<h3 id="17-8-修改表"><a href="#17-8-修改表" class="headerlink" title="17.8 修改表"></a>17.8 修改表</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line"># MySQL对大小写是不敏感的</span><br><span class="line">&quot;&quot;&quot;</span><br><span class="line">1 修改表名</span><br><span class="line">	alter table 表名 rename 新表名;</span><br><span class="line"></span><br><span class="line">2 增加字段</span><br><span class="line">	alter table 表名 add 字段名 字段类型(宽度)  约束条件;</span><br><span class="line">	alter table 表名 add 字段名 字段类型(宽度)  约束条件 first;</span><br><span class="line">	alter table 表名 add 字段名 字段类型(宽度)  约束条件 after 字段名;</span><br><span class="line"></span><br><span class="line">3 删除字段</span><br><span class="line">	alter table 表名 drop 字段名;</span><br><span class="line"></span><br><span class="line">4 修改字段</span><br><span class="line">	alter table 表名 modify 字段名 字段类型(宽度) 约束条件;</span><br><span class="line">	</span><br><span class="line">	alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件;</span><br><span class="line">	</span><br><span class="line">&quot;&quot;&quot;</span><br></pre></td></tr></table></figure>

<h3 id="17-9-复制表"><a href="#17-9-复制表" class="headerlink" title="17.9 复制表"></a>17.9 复制表</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">&quot;&quot;&quot;</span><br><span class="line">我们sql语句查询的结果其实也是一张虚拟表</span><br><span class="line">&quot;&quot;&quot;</span><br><span class="line">create table 表名 select * from 旧表;  不能复制主键 外键 ...</span><br><span class="line"></span><br><span class="line">create table new_dep2 select * from dep where id&gt;3;</span><br></pre></td></tr></table></figure>

<h2 id="十八、前期表准备"><a href="#十八、前期表准备" class="headerlink" title="十八、前期表准备"></a>十八、前期表准备</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br></pre></td><td class="code"><pre><span class="line">create table emp(</span><br><span class="line">  <span class="built_in">id</span> <span class="built_in">int</span> <span class="keyword">not</span> null unique auto_increment,</span><br><span class="line">  name varchar(<span class="number">20</span>) <span class="keyword">not</span> null,</span><br><span class="line">  sex enum(<span class="string">&#x27;male&#x27;</span>,<span class="string">&#x27;female&#x27;</span>) <span class="keyword">not</span> null default <span class="string">&#x27;male&#x27;</span>, <span class="comment">#大部分是男的</span></span><br><span class="line">  age <span class="built_in">int</span>(<span class="number">3</span>) unsigned <span class="keyword">not</span> null default <span class="number">28</span>,</span><br><span class="line">  hire_date date <span class="keyword">not</span> null,</span><br><span class="line">  post varchar(<span class="number">50</span>),</span><br><span class="line">  post_comment varchar(<span class="number">100</span>),</span><br><span class="line">  salary double(<span class="number">15</span>,<span class="number">2</span>),</span><br><span class="line">  office <span class="built_in">int</span>, <span class="comment">#一个部门一个屋子</span></span><br><span class="line">  depart_id <span class="built_in">int</span></span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"><span class="comment">#插入记录</span></span><br><span class="line"><span class="comment">#三个部门：教学，销售，运营</span></span><br><span class="line">    insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values</span><br><span class="line">    (<span class="string">&#x27;jason&#x27;</span>,<span class="string">&#x27;male&#x27;</span>,<span class="number">18</span>,<span class="string">&#x27;20170301&#x27;</span>,<span class="string">&#x27;张江第一帅形象代言&#x27;</span>,<span class="number">7300.33</span>,<span class="number">401</span>,<span class="number">1</span>), <span class="comment">#以下是教学部</span></span><br><span class="line">    (<span class="string">&#x27;tom&#x27;</span>,<span class="string">&#x27;male&#x27;</span>,<span class="number">78</span>,<span class="string">&#x27;20150302&#x27;</span>,<span class="string">&#x27;teacher&#x27;</span>,<span class="number">1000000.31</span>,<span class="number">401</span>,<span class="number">1</span>),</span><br><span class="line">    (<span class="string">&#x27;kevin&#x27;</span>,<span class="string">&#x27;male&#x27;</span>,<span class="number">81</span>,<span class="string">&#x27;20130305&#x27;</span>,<span class="string">&#x27;teacher&#x27;</span>,<span class="number">8300</span>,<span class="number">401</span>,<span class="number">1</span>),</span><br><span class="line">    (<span class="string">&#x27;tony&#x27;</span>,<span class="string">&#x27;male&#x27;</span>,<span class="number">73</span>,<span class="string">&#x27;20140701&#x27;</span>,<span class="string">&#x27;teacher&#x27;</span>,<span class="number">3500</span>,<span class="number">401</span>,<span class="number">1</span>),</span><br><span class="line">    (<span class="string">&#x27;owen&#x27;</span>,<span class="string">&#x27;male&#x27;</span>,<span class="number">28</span>,<span class="string">&#x27;20121101&#x27;</span>,<span class="string">&#x27;teacher&#x27;</span>,<span class="number">2100</span>,<span class="number">401</span>,<span class="number">1</span>),</span><br><span class="line">    (<span class="string">&#x27;jack&#x27;</span>,<span class="string">&#x27;female&#x27;</span>,<span class="number">18</span>,<span class="string">&#x27;20110211&#x27;</span>,<span class="string">&#x27;teacher&#x27;</span>,<span class="number">9000</span>,<span class="number">401</span>,<span class="number">1</span>),</span><br><span class="line">    (<span class="string">&#x27;jenny&#x27;</span>,<span class="string">&#x27;male&#x27;</span>,<span class="number">18</span>,<span class="string">&#x27;19000301&#x27;</span>,<span class="string">&#x27;teacher&#x27;</span>,<span class="number">30000</span>,<span class="number">401</span>,<span class="number">1</span>),</span><br><span class="line">    (<span class="string">&#x27;sank&#x27;</span>,<span class="string">&#x27;male&#x27;</span>,<span class="number">48</span>,<span class="string">&#x27;20101111&#x27;</span>,<span class="string">&#x27;teacher&#x27;</span>,<span class="number">10000</span>,<span class="number">401</span>,<span class="number">1</span>),</span><br><span class="line">    (<span class="string">&#x27;哈哈&#x27;</span>,<span class="string">&#x27;female&#x27;</span>,<span class="number">48</span>,<span class="string">&#x27;20150311&#x27;</span>,<span class="string">&#x27;sale&#x27;</span>,<span class="number">3000.13</span>,<span class="number">402</span>,<span class="number">2</span>),<span class="comment">#以下是销售部门</span></span><br><span class="line">    (<span class="string">&#x27;呵呵&#x27;</span>,<span class="string">&#x27;female&#x27;</span>,<span class="number">38</span>,<span class="string">&#x27;20101101&#x27;</span>,<span class="string">&#x27;sale&#x27;</span>,<span class="number">2000.35</span>,<span class="number">402</span>,<span class="number">2</span>),</span><br><span class="line">    (<span class="string">&#x27;西西&#x27;</span>,<span class="string">&#x27;female&#x27;</span>,<span class="number">18</span>,<span class="string">&#x27;20110312&#x27;</span>,<span class="string">&#x27;sale&#x27;</span>,<span class="number">1000.37</span>,<span class="number">402</span>,<span class="number">2</span>),</span><br><span class="line">    (<span class="string">&#x27;乐乐&#x27;</span>,<span class="string">&#x27;female&#x27;</span>,<span class="number">18</span>,<span class="string">&#x27;20160513&#x27;</span>,<span class="string">&#x27;sale&#x27;</span>,<span class="number">3000.29</span>,<span class="number">402</span>,<span class="number">2</span>),</span><br><span class="line">    (<span class="string">&#x27;拉拉&#x27;</span>,<span class="string">&#x27;female&#x27;</span>,<span class="number">28</span>,<span class="string">&#x27;20170127&#x27;</span>,<span class="string">&#x27;sale&#x27;</span>,<span class="number">4000.33</span>,<span class="number">402</span>,<span class="number">2</span>),</span><br><span class="line">    (<span class="string">&#x27;僧龙&#x27;</span>,<span class="string">&#x27;male&#x27;</span>,<span class="number">28</span>,<span class="string">&#x27;20160311&#x27;</span>,<span class="string">&#x27;operation&#x27;</span>,<span class="number">10000.13</span>,<span class="number">403</span>,<span class="number">3</span>), <span class="comment">#以下是运营部门</span></span><br><span class="line">    (<span class="string">&#x27;程咬金&#x27;</span>,<span class="string">&#x27;male&#x27;</span>,<span class="number">18</span>,<span class="string">&#x27;19970312&#x27;</span>,<span class="string">&#x27;operation&#x27;</span>,<span class="number">20000</span>,<span class="number">403</span>,<span class="number">3</span>),</span><br><span class="line">    (<span class="string">&#x27;程咬银&#x27;</span>,<span class="string">&#x27;female&#x27;</span>,<span class="number">18</span>,<span class="string">&#x27;20130311&#x27;</span>,<span class="string">&#x27;operation&#x27;</span>,<span class="number">19000</span>,<span class="number">403</span>,<span class="number">3</span>),</span><br><span class="line">    (<span class="string">&#x27;程咬铜&#x27;</span>,<span class="string">&#x27;male&#x27;</span>,<span class="number">18</span>,<span class="string">&#x27;20150411&#x27;</span>,<span class="string">&#x27;operation&#x27;</span>,<span class="number">18000</span>,<span class="number">403</span>,<span class="number">3</span>),</span><br><span class="line">    (<span class="string">&#x27;程咬铁&#x27;</span>,<span class="string">&#x27;female&#x27;</span>,<span class="number">18</span>,<span class="string">&#x27;20140512&#x27;</span>,<span class="string">&#x27;operation&#x27;</span>,<span class="number">17000</span>,<span class="number">403</span>,<span class="number">3</span>);</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment"># 当表字段特别多 展示的时候错乱 可以使用\G分行展示</span></span><br><span class="line">    select * <span class="keyword">from</span> emp\G;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 个别同学的电脑在插入中文的时候还是会出现乱码或者空白的现象 你可以将字符编码统一设置成GBK</span></span><br></pre></td></tr></table></figure>

<h2 id="十九、几个重要关键字的执行顺序"><a href="#十九、几个重要关键字的执行顺序" class="headerlink" title="十九、几个重要关键字的执行顺序"></a>十九、几个重要关键字的执行顺序</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 书写顺序</span></span><br><span class="line">	select <span class="built_in">id</span>,name <span class="keyword">from</span> emp where <span class="built_in">id</span> &gt; <span class="number">3</span>;</span><br><span class="line"><span class="comment"># 执行顺序</span></span><br><span class="line">    <span class="keyword">from</span></span><br><span class="line">    where</span><br><span class="line">    select</span><br><span class="line"></span><br><span class="line">    <span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">    虽然执行顺序和书写顺序不一致 你在写sql语句的时候可能不知道怎么写</span></span><br><span class="line"><span class="string">    你就按照书写顺序的方式写sql</span></span><br><span class="line"><span class="string">        select * 先用*号占位</span></span><br><span class="line"><span class="string">        之后去补全后面的sql语句</span></span><br><span class="line"><span class="string">        最后将*号替换后你想要的具体字段</span></span><br><span class="line"><span class="string">		 这里先理解</span></span><br><span class="line"><span class="string">    &quot;&quot;&quot;</span></span><br></pre></td></tr></table></figure>

<h2 id="二十、where筛选条件"><a href="#二十、where筛选条件" class="headerlink" title="二十、where筛选条件"></a>二十、where筛选条件</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 作用:是对整体数据的一个筛选操作</span></span><br><span class="line"><span class="comment"># 1.查询id大于等于3小于等于6的数据</span></span><br><span class="line">    select <span class="built_in">id</span>,name,age <span class="keyword">from</span> emp where <span class="built_in">id</span>&gt;=<span class="number">3</span> <span class="keyword">and</span> <span class="built_in">id</span>&lt;=<span class="number">6</span>;</span><br><span class="line">    select <span class="built_in">id</span>,name <span class="keyword">from</span> emp where <span class="built_in">id</span> between <span class="number">3</span> <span class="keyword">and</span> <span class="number">6</span>;  两者等价</span><br><span class="line"></span><br><span class="line"><span class="comment"># 2.查询薪资是20000或者18000或者17000的数据</span></span><br><span class="line">    select * <span class="keyword">from</span> emp where salary=<span class="number">20000</span> <span class="keyword">or</span> salary=<span class="number">18000</span> <span class="keyword">or</span> salary=<span class="number">17000</span>;</span><br><span class="line">    select * <span class="keyword">from</span> emp where salary <span class="keyword">in</span> (<span class="number">20000</span>,<span class="number">18000</span>,<span class="number">17000</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment"># 3.查询员工姓名中包含字母o的员工的姓名和薪资</span></span><br><span class="line">    <span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">    模糊查询</span></span><br><span class="line"><span class="string">        like</span></span><br><span class="line"><span class="string">            %  匹配任意多个字符</span></span><br><span class="line"><span class="string">            _  匹配任意单个字符</span></span><br><span class="line"><span class="string">    &quot;&quot;&quot;</span></span><br><span class="line">	select name,salary <span class="keyword">from</span> emp where name like <span class="string">&#x27;%o%&#x27;</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 4.查询员工姓名是由四个字符组成的 姓名和薪资  char_length()   _</span></span><br><span class="line">    select name,salary <span class="keyword">from</span> emp where name like <span class="string">&#x27;____&#x27;</span>;</span><br><span class="line">    select name,salary <span class="keyword">from</span> emp where char_length(name) = <span class="number">4</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 5.查询id小于3或者id大于6的数据</span></span><br><span class="line">    select * <span class="keyword">from</span> emp where <span class="built_in">id</span> <span class="keyword">not</span> between <span class="number">3</span> <span class="keyword">and</span> <span class="number">6</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 6.查询薪资不在20000,18000,17000范围的数据</span></span><br><span class="line">    select * <span class="keyword">from</span> emp where salary <span class="keyword">not</span> <span class="keyword">in</span> (<span class="number">20000</span>,<span class="number">18000</span>,<span class="number">17000</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment"># 7.查询岗位描述为空的员工姓名和岗位名  针对null不用等号 用is</span></span><br><span class="line">    select name,post <span class="keyword">from</span> emp where post_comment = NULL;</span><br><span class="line">    select name,post <span class="keyword">from</span> emp where post_comment <span class="keyword">is</span> NULL;</span><br></pre></td></tr></table></figure>

<h2 id="二十一、group-by分组"><a href="#二十一、group-by分组" class="headerlink" title="二十一、group by分组"></a>二十一、group by分组</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 分组实际应用场景  分组应用场景非常的多</span></span><br><span class="line">	男女比例</span><br><span class="line">    部门平均薪资</span><br><span class="line">    部门秃头率</span><br><span class="line">    国家之间数据统计</span><br><span class="line"></span><br><span class="line"><span class="comment"># 1	按照部门分组</span></span><br><span class="line">    select * <span class="keyword">from</span> emp group by post;</span><br><span class="line">    <span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">    分组之后 最小可操作单位应该是组 还不再是组内的单个数据</span></span><br><span class="line"><span class="string">        上述命令在你没有设置严格模式的时候是可正常执行的 返回的是分组之后 每个组的第一条数据 但是这不符合分组的规范:分组之后不应该考虑单个数据 而应该以组为操作单位(分组之后 没办法直接获取组内单个数据)</span></span><br><span class="line"><span class="string">        如果设置了严格模式 那么上述命令会直接报错 </span></span><br><span class="line"><span class="string">    &quot;&quot;&quot;</span></span><br><span class="line">    <span class="built_in">set</span> <span class="keyword">global</span> sql_mode = <span class="string">&#x27;strict_trans_tables,only_full_group_by&#x27;</span>;</span><br><span class="line"></span><br><span class="line">    设置严格模式之后  分组 默认只能拿到分组的依据</span><br><span class="line">    select post <span class="keyword">from</span> emp group by post;  </span><br><span class="line">    按照什么分组就只能拿到分组 其他字段不能直接获取 需要借助于一些方法(聚合函数)</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">什么时候需要分组啊？？？</span></span><br><span class="line"><span class="string">	关键字 </span></span><br><span class="line"><span class="string">		每个 平均 最高 最低 </span></span><br><span class="line"><span class="string">	</span></span><br><span class="line"><span class="string">	聚合函数</span></span><br><span class="line"><span class="string">		max</span></span><br><span class="line"><span class="string">		min</span></span><br><span class="line"><span class="string">		sum</span></span><br><span class="line"><span class="string">		count</span></span><br><span class="line"><span class="string">		avg</span></span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="comment"># 1.获取每个部门的最高薪资</span></span><br><span class="line">    select post,<span class="built_in">max</span>(salary) <span class="keyword">from</span> emp group by post;</span><br><span class="line">    select post <span class="keyword">as</span> <span class="string">&#x27;部门&#x27;</span>,<span class="built_in">max</span>(salary) <span class="keyword">as</span> <span class="string">&#x27;最高薪资&#x27;</span> <span class="keyword">from</span> emp group by post;</span><br><span class="line">    select post <span class="string">&#x27;部门&#x27;</span>,<span class="built_in">max</span>(salary) <span class="string">&#x27;最高薪资&#x27;</span> <span class="keyword">from</span> emp group by post;</span><br><span class="line"><span class="comment"># as可以给字段起别名 也可以直接省略不写 但是不推荐 因为省略的话语意不明确 容易错乱</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 2.获取每个部门的最低薪资</span></span><br><span class="line">	select post,<span class="built_in">min</span>(salary) <span class="keyword">from</span> emp group by post;</span><br><span class="line"><span class="comment"># 3.获取每个部门的平均薪资</span></span><br><span class="line">select post,avg(salary) <span class="keyword">from</span> emp group by post;</span><br><span class="line"><span class="comment"># 4.获取每个部门的工资总和</span></span><br><span class="line">	select post,<span class="built_in">sum</span>(salary) <span class="keyword">from</span> emp group by post;</span><br><span class="line"><span class="comment"># 5.获取每个部门的人数</span></span><br><span class="line">    select post,count(<span class="built_in">id</span>) <span class="keyword">from</span> emp group by post;  <span class="comment"># 常用 符合逻辑</span></span><br><span class="line">    select post,count(salary) <span class="keyword">from</span> emp group by post;</span><br><span class="line">    select post,count(age) <span class="keyword">from</span> emp group by post;</span><br><span class="line">    select post,count(post_comment) <span class="keyword">from</span> emp group by post;  null不行</span><br><span class="line"></span><br><span class="line"><span class="comment"># 6.查询分组之后的部门名称和每个部门下所有的员工姓名 </span></span><br><span class="line"><span class="comment"># group_concat不单单可以支持你获取分组之后的其他字段值 还支持拼接操作</span></span><br><span class="line">    select post,group_concat(name) <span class="keyword">from</span> emp group by post;</span><br><span class="line">    select post,group_concat(name,<span class="string">&#x27;_DSB&#x27;</span>) <span class="keyword">from</span> emp group by post;</span><br><span class="line">    select post,group_concat(name,<span class="string">&#x27;:&#x27;</span>,salary) <span class="keyword">from</span> emp group by post;</span><br><span class="line"><span class="comment"># concat不分组的时候用 </span></span><br><span class="line">	select concat(<span class="string">&#x27;NAME:&#x27;</span>,name),concat(<span class="string">&#x27;SAL:&#x27;</span>,salary) <span class="keyword">from</span> emp;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 补充 as语法不单单可以给字段起别名 还可以给表临时起别名</span></span><br><span class="line">    select emp.<span class="built_in">id</span>,emp.name <span class="keyword">from</span> emp;  </span><br><span class="line">    select emp.<span class="built_in">id</span>,emp.name <span class="keyword">from</span> emp <span class="keyword">as</span> t1;   报错</span><br><span class="line">    select t1.<span class="built_in">id</span>,t1.name <span class="keyword">from</span> emp <span class="keyword">as</span> t1;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 查询每个人的年薪  12薪</span></span><br><span class="line">	select name,salary*<span class="number">12</span> <span class="keyword">from</span> emp;</span><br></pre></td></tr></table></figure>

<h2 id="二十二、分组注意事项"><a href="#二十二、分组注意事项" class="headerlink" title="二十二、分组注意事项"></a>二十二、分组注意事项</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 关键字where和group by同时出现的时候group by必须在where的后面</span></span><br><span class="line">    where先对整体数据进行过滤之后再分组操作</span><br><span class="line">    where筛选条件不能使用聚合函数</span><br><span class="line">    select <span class="built_in">id</span>,name,age <span class="keyword">from</span> emp where <span class="built_in">max</span>(salary) &gt; <span class="number">3000</span>;</span><br><span class="line"></span><br><span class="line">    select <span class="built_in">max</span>(salary) <span class="keyword">from</span> emp;  <span class="comment"># 不分组 默认整体就是一组</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 统计各部门年龄在30岁以上的员工平均薪资</span></span><br><span class="line">	<span class="number">1</span> 先求所有年龄大于<span class="number">30</span>岁的员工</span><br><span class="line">    	select * <span class="keyword">from</span> emp where age&gt;<span class="number">30</span>;</span><br><span class="line">    <span class="number">2</span> 再对结果进行分组</span><br><span class="line">     	select * <span class="keyword">from</span> emp where age&gt;<span class="number">30</span> group by post;</span><br><span class="line">    </span><br><span class="line">    select post,avg(salary) <span class="keyword">from</span> emp where age&gt;<span class="number">30</span> group by post;</span><br></pre></td></tr></table></figure>

<h2 id="二十三、having分组之后的筛选条件"><a href="#二十三、having分组之后的筛选条件" class="headerlink" title="二十三、having分组之后的筛选条件"></a>二十三、having分组之后的筛选条件</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">having的语法根where是一致的</span></span><br><span class="line"><span class="string">只不过having是在分组之后进行的过滤操作</span></span><br><span class="line"><span class="string">即having是可以直接使用聚合函数的</span></span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="comment"># 统计各部门年龄在30岁以上的员工平均工资并且保留平均薪资大于10000的部门</span></span><br><span class="line">    select post,avg(salary) <span class="keyword">from</span> emp </span><br><span class="line">            where age&gt;<span class="number">30</span> </span><br><span class="line">            group by post</span><br><span class="line">            having avg(salary) &gt; <span class="number">10000</span></span><br><span class="line">            ;</span><br></pre></td></tr></table></figure>

<h2 id="二十四、distinct去重"><a href="#二十四、distinct去重" class="headerlink" title="二十四、distinct去重"></a>二十四、distinct去重</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">一定要注意 必须是完全一样的数据才可以去重！！！</span></span><br><span class="line"><span class="string">一定不要将逐渐忽视了 有逐渐存在的情况下 是不可能去重的</span></span><br><span class="line"><span class="string">[</span></span><br><span class="line"><span class="string">&#123;&#x27;id&#x27;:1,&#x27;name&#x27;:&#x27;jason&#x27;,&#x27;age&#x27;:18&#125;,</span></span><br><span class="line"><span class="string">&#123;&#x27;id&#x27;:2,&#x27;name&#x27;:&#x27;jason&#x27;,&#x27;age&#x27;:18&#125;,</span></span><br><span class="line"><span class="string">&#123;&#x27;id&#x27;:3,&#x27;name&#x27;:&#x27;egon&#x27;,&#x27;age&#x27;:18&#125;</span></span><br><span class="line"><span class="string">]</span></span><br><span class="line"><span class="string">ORM  对象关系映射   让不懂SQL语句的人也能够非常牛逼的操作数据库</span></span><br><span class="line"><span class="string">表								类</span></span><br><span class="line"><span class="string">一条条的数据						对象</span></span><br><span class="line"><span class="string">字段对应的值						对象的属性</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">你再写类 就意味着在创建表</span></span><br><span class="line"><span class="string">用类生成对象 就意味着再创建数据</span></span><br><span class="line"><span class="string">对象点属性 就是在获取数据字段对应的值</span></span><br><span class="line"><span class="string">目的就是减轻python程序员的压力 只需要会python面向对象的知识点就可以操作MySQL</span></span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line">select distinct <span class="built_in">id</span>,age <span class="keyword">from</span> emp;</span><br><span class="line">select distinct age <span class="keyword">from</span> emp;</span><br></pre></td></tr></table></figure>

<h2 id="二十五、order-by排序"><a href="#二十五、order-by排序" class="headerlink" title="二十五、order by排序"></a>二十五、order by排序</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line">select * <span class="keyword">from</span> emp order by salary;</span><br><span class="line">select * <span class="keyword">from</span> emp order by salary asc;</span><br><span class="line">select * <span class="keyword">from</span> emp order by salary desc;</span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">order by默认是升序  asc 该asc可以省略不写</span></span><br><span class="line"><span class="string">也可以修改为降序     desc</span></span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line">select * <span class="keyword">from</span> emp order by age desc,salary asc;</span><br><span class="line"><span class="comment"># 先按照age降序排  如果碰到age相同 则再按照salary升序排</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 统计各部门年龄在10岁以上的员工平均工资并且保留平均薪资大于1000的部门,然后对平均工资降序排序</span></span><br><span class="line">	select post,avg(salary) <span class="keyword">from</span> emp </span><br><span class="line">		where age&gt;<span class="number">10</span> </span><br><span class="line">    	group by post</span><br><span class="line">        having avg(salary) &gt; <span class="number">1000</span></span><br><span class="line">        order by avg(salary) desc</span><br><span class="line">        ;</span><br></pre></td></tr></table></figure>

<h2 id="二十六、limit限制展示条数"><a href="#二十六、limit限制展示条数" class="headerlink" title="二十六、limit限制展示条数"></a>二十六、limit限制展示条数</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">select * <span class="keyword">from</span> emp;</span><br><span class="line"><span class="string">&quot;&quot;&quot;针对数据过多的情况 我们通常都是做分页处理&quot;&quot;&quot;</span></span><br><span class="line">    select * <span class="keyword">from</span> emp limit <span class="number">3</span>;  <span class="comment"># 只展示三条数据</span></span><br><span class="line"></span><br><span class="line">    select * <span class="keyword">from</span> emp limit <span class="number">0</span>,<span class="number">5</span>;</span><br><span class="line">    select * <span class="keyword">from</span> emp limit <span class="number">5</span>,<span class="number">5</span>;</span><br><span class="line">    第一个参数是起始位置</span><br><span class="line">    第二个参数是展示条数</span><br></pre></td></tr></table></figure>

<h2 id="二十七、正则"><a href="#二十七、正则" class="headerlink" title="二十七、正则"></a>二十七、正则</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select * <span class="keyword">from</span> emp where name regexp <span class="string">&#x27;^j.*(n|y)$&#x27;</span>;</span><br></pre></td></tr></table></figure>



<h2 id="二十八、多表操作"><a href="#二十八、多表操作" class="headerlink" title="二十八、多表操作"></a>二十八、多表操作</h2><h3 id="28-1-前期表准备"><a href="#28-1-前期表准备" class="headerlink" title="28.1 前期表准备"></a>28.1 前期表准备</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#建表</span></span><br><span class="line">    create table dep(</span><br><span class="line">    <span class="built_in">id</span> <span class="built_in">int</span>,</span><br><span class="line">    name varchar(<span class="number">20</span>) </span><br><span class="line">    );</span><br><span class="line"></span><br><span class="line">    create table emp(</span><br><span class="line">    <span class="built_in">id</span> <span class="built_in">int</span> primary key auto_increment,</span><br><span class="line">    name varchar(<span class="number">20</span>),</span><br><span class="line">    sex enum(<span class="string">&#x27;male&#x27;</span>,<span class="string">&#x27;female&#x27;</span>) <span class="keyword">not</span> null default <span class="string">&#x27;male&#x27;</span>,</span><br><span class="line">    age <span class="built_in">int</span>,</span><br><span class="line">    dep_id <span class="built_in">int</span></span><br><span class="line">    );</span><br><span class="line"></span><br><span class="line"><span class="comment">#插入数据</span></span><br><span class="line">    insert into dep values</span><br><span class="line">    (<span class="number">200</span>,<span class="string">&#x27;技术&#x27;</span>),</span><br><span class="line">    (<span class="number">201</span>,<span class="string">&#x27;人力资源&#x27;</span>),</span><br><span class="line">    (<span class="number">202</span>,<span class="string">&#x27;销售&#x27;</span>),</span><br><span class="line">    (<span class="number">203</span>,<span class="string">&#x27;运营&#x27;</span>);</span><br><span class="line"></span><br><span class="line">    insert into emp(name,sex,age,dep_id) values</span><br><span class="line">    (<span class="string">&#x27;jason&#x27;</span>,<span class="string">&#x27;male&#x27;</span>,<span class="number">18</span>,<span class="number">200</span>),</span><br><span class="line">    (<span class="string">&#x27;egon&#x27;</span>,<span class="string">&#x27;female&#x27;</span>,<span class="number">48</span>,<span class="number">201</span>),</span><br><span class="line">    (<span class="string">&#x27;kevin&#x27;</span>,<span class="string">&#x27;male&#x27;</span>,<span class="number">18</span>,<span class="number">201</span>),</span><br><span class="line">    (<span class="string">&#x27;nick&#x27;</span>,<span class="string">&#x27;male&#x27;</span>,<span class="number">28</span>,<span class="number">202</span>),</span><br><span class="line">    (<span class="string">&#x27;owen&#x27;</span>,<span class="string">&#x27;male&#x27;</span>,<span class="number">18</span>,<span class="number">203</span>),</span><br><span class="line">    (<span class="string">&#x27;jerry&#x27;</span>,<span class="string">&#x27;female&#x27;</span>,<span class="number">18</span>,<span class="number">204</span>);</span><br></pre></td></tr></table></figure>

<h3 id="28-2表查询"><a href="#28-2表查询" class="headerlink" title="28.2表查询"></a>28.2表查询</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br></pre></td><td class="code"><pre><span class="line">select * <span class="keyword">from</span> dep,emp;  <span class="comment"># 结果   笛卡尔积</span></span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">了解即可 不知道也没关系</span></span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"></span><br><span class="line">	select * <span class="keyword">from</span> emp,dep where emp.dep_id = dep.<span class="built_in">id</span>;</span><br><span class="line"></span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">MySQL也知道 你在后面查询数据过程中 肯定会经常用到拼表操作 </span></span><br><span class="line"><span class="string">所以特地给你开设了对应的方法</span></span><br><span class="line"><span class="string">	inner join  内连接</span></span><br><span class="line"><span class="string">	left join   左连接</span></span><br><span class="line"><span class="string">	right join  右连接</span></span><br><span class="line"><span class="string">	union		全连接</span></span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="comment"># inner join  内连接</span></span><br><span class="line">	select * <span class="keyword">from</span> emp inner join dep on emp.dep_id = dep.<span class="built_in">id</span>;</span><br><span class="line"><span class="comment"># 只拼接两张表中公有的数据部分</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># left join   左连接</span></span><br><span class="line">	select * <span class="keyword">from</span> emp left join dep on emp.dep_id = dep.<span class="built_in">id</span>;</span><br><span class="line"><span class="comment"># 左表所有的数据都展示出来 没有对应的项就用NULL</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># right join  右连接</span></span><br><span class="line">	select * <span class="keyword">from</span> emp right join dep on emp.dep_id = dep.<span class="built_in">id</span>;</span><br><span class="line"><span class="comment"># 右表所有的数据都展示出来 没有对应的项就用NULL</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># union		全连接  左右两表所有的数据都展示出来</span></span><br><span class="line">	select * <span class="keyword">from</span> emp left join dep on emp.dep_id = dep.<span class="built_in">id</span></span><br><span class="line">	union</span><br><span class="line">	select * <span class="keyword">from</span> emp right join dep on emp.dep_id = dep.<span class="built_in">id</span>;</span><br></pre></td></tr></table></figure>

<h3 id="28-3-子查询"><a href="#28-3-子查询" class="headerlink" title="28.3 子查询"></a>28.3 子查询</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">子查询就是我们平时解决问题的思路</span></span><br><span class="line"><span class="string">	分步骤解决问题</span></span><br><span class="line"><span class="string">		第一步</span></span><br><span class="line"><span class="string">		第二步</span></span><br><span class="line"><span class="string">		...</span></span><br><span class="line"><span class="string">将一个查询语句的结果当做另外一个查询语句的条件去用</span></span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="comment"># 查询部门是技术或者人力资源的员工信息</span></span><br><span class="line">	<span class="number">1</span> 先获取部门的<span class="built_in">id</span>号</span><br><span class="line">    <span class="number">2</span> 再去员工表里面筛选出对应的员工</span><br><span class="line">    select <span class="built_in">id</span> <span class="keyword">from</span> dep where name=<span class="string">&#x27;技术&#x27;</span> <span class="keyword">or</span> name = <span class="string">&#x27;人力资源&#x27;</span>;</span><br><span class="line">    </span><br><span class="line">    select name <span class="keyword">from</span> emp where dep_id <span class="keyword">in</span> (<span class="number">200</span>,<span class="number">201</span>);</span><br><span class="line">    </span><br><span class="line">    </span><br><span class="line">    select * <span class="keyword">from</span> emp where dep_id <span class="keyword">in</span> (select <span class="built_in">id</span> <span class="keyword">from</span> dep where name=<span class="string">&#x27;技术&#x27;</span> <span class="keyword">or</span> name = <span class="string">&#x27;人力资源&#x27;</span>);</span><br></pre></td></tr></table></figure>

<h3 id="28-4-总结"><a href="#28-4-总结" class="headerlink" title="28.4 总结"></a>28.4 总结</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">表的查询结果可以作为其他表的查询条件</span><br><span class="line">也可以通过起别名的方式把它作为一个张虚拟表根其他表关联</span><br><span class="line"></span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">多表查询就两种方式</span></span><br><span class="line"><span class="string">	先拼接表再查询</span></span><br><span class="line"><span class="string">	子查询 一步一步来</span></span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br></pre></td></tr></table></figure>

<p>​                                                                                                                                                                                                      </p>

            </div>

            <!-- Post Comments -->
            
    <!-- 使用 DISQUS_CLICK -->
<div id="disqus-comment">
    <div id="disqus_thread"></div>

<!-- add animation -->
<style>
	.disqus_click_btn {
            line-height: 30px;
            margin: 0;
            min-width: 50px;
            padding: 0 14px;
            display: inline-block;
            font-family: "Roboto", "Helvetica", "Arial", sans-serif;
            font-size: 14px;
            font-weight: 400;
            text-transform: uppercase;
            letter-spacing: 0;
            overflow: hidden;
            will-change: box-shadow;
            transition: box-shadow .2s cubic-bezier(.4, 0, 1, 1), background-color .2s cubic-bezier(.4, 0, .2, 1), color .2s cubic-bezier(.4, 0, .2, 1);
            outline: 0;
            cursor: pointer;
            text-decoration: none;
            text-align: center;
            vertical-align: middle;
            border: 0;
            background: rgba(158, 158, 158, .2);
            box-shadow: 0 2px 2px 0 rgba(0, 0, 0, .14), 0 3px 1px -2px rgba(0, 0, 0, .2), 0 1px 5px 0 rgba(0, 0, 0, .12);
            color: #fff;
            background-color: #7EC0EE;
            text-shadow: 0
        }
</style>
	
<div class="btn_click_load" id="disqus_bt"> 
    <button class="disqus_click_btn">点击查看评论</button>
</div>

<!--
<script type="text/javascript">
$('.btn_click_load').click(function() {
    /* * * CONFIGURATION VARIABLES: EDIT BEFORE PASTING INTO YOUR WEBPAGE * * */
    var disqus_shortname = 'http-miccall-tech'; // required: replace example with your forum shortname

    /* * * DON'T EDIT BELOW THIS LINE * * */
    (function() {
      var dsq = document.createElement('script'); dsq.type = 'text/javascript'; dsq.async = true;
      dsq.src = '//' + disqus_shortname + '.disqus.com/embed.js';
      (document.getElementsByTagName('head')[0] || document.getElementsByTagName('body')[0]).appendChild(dsq);
    })();

    document.getElementById('disqus_bt').style.display = "none";
});
</script>
-->
<script type="text/javascript">
    var disqus_config = function () {
        this.page.url = 'https://gitee.com/AmeX/amelin-ting.git/2018/06/02/MySQL------cmd/';  // Replace PAGE_URL with your page's canonical URL variable
        this.page.identifier = 'https://gitee.com/AmeX/amelin-ting.git/2018/06/02/MySQL------cmd/'; // Replace PAGE_IDENTIFIER with your page's unique identifier variable
    };
</script>

<script type="text/javascript">
    $('.btn_click_load').click(function() {  //click to load comments
        (function() { // DON'T EDIT BELOW THIS LINE
            var d = document;
            var s = d.createElement('script');
            s.src = '//http-miccall-tech.disqus.com/embed.js';
            s.setAttribute('data-timestamp', + new Date());
            (d.head || d.body).appendChild(s);
        })();
        $('.btn_click_load').css('display','none');
    });
</script>
</div>
<style>
    #disqus-comment{
        background-color: #eee;
        padding: 2pc;
    }
</style>


        </div>
        <!-- Copyright 版权 start -->
                <div id="copyright">
            <ul>
                <li>&copy;Powered By <a target="_blank" rel="noopener" href="https://hexo.io/zh-cn/" style="border-bottom: none;">hexo</a></li>
                <li>Design: <a target="_blank" rel="noopener" href="http://miccall.tech " style="border-bottom: none;">miccall</a></li>
            </ul>
            
                <span id="busuanzi_container_site_pv">本站总访问量<span id="busuanzi_value_site_pv"></span>次</span>
			
        </div>
    </div>
</body>



 	
</html>
